﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;

namespace StudentManagementSystem
{
	public class DBHelper
	{
        private static string connectionString = "Data Source=student.db;Version=3;"; // 数据库连接字符串
        private static SQLiteConnection connection; // 数据库连接对象

        // 静态构造函数，创建数据库连接对象
        static DBHelper()
        {
            connection = new SQLiteConnection(connectionString);
        }

        // 打开数据库连接
        public static void OpenConnection()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        // 关闭数据库连接
        public static void CloseConnection()
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }

        // 执行非查询命令，返回受影响的行数
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command.ExecuteNonQuery();
            }
        }

        // 执行查询命令，返回第一行第一列的值
        public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command.ExecuteScalar();
            }
        }

        // 执行查询命令，返回一个数据读取器对象
        public static SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command.ExecuteReader();
            }
        }

        // 添加学校
        public static void AddSchool(School school)
        {
            string sql = "insert into school (id, name) values (@id, @name)";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", school.Id),
                new SQLiteParameter("@name", school.Name)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"添加了一个学校，编号：{school.Id}，名称：{school.Name}");
        }

        // 删除学校
        public static void DeleteSchool(School school)
        {
            string sql = "delete from school where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", school.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"删除了一个学校，编号：{school.Id}，名称：{school.Name}");
        }

        // 修改学校
        public static void UpdateSchool(School school)
        {
            string sql = "update school set name = @name where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", school.Id),
                new SQLiteParameter("@name", school.Name)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"修改了一个学校，编号：{school.Id}，名称：{school.Name}");
        }

        // 查询所有学校
        public static List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();
            string sql = "select * from school";
            using (SQLiteDataReader reader = ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    School school = new School(id, name);
                    schools.Add(school);
                }
            }
            LogHelper.AddLog($"查询了所有学校");
            return schools;
        }

        // 根据编号查询学校
        public static School GetSchoolById(int id)
        {
            School school = null;
            string sql = "select * from school where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", id)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    string name = reader.GetString(1);
                    school = new School(id, name);
                }
            }
            LogHelper.AddLog($"根据编号查询了一个学校，编号：{id}");
            return school;
        }

        // 根据名称查询学校
        public static School GetSchoolByName(string name)
        {
            School school = null;
            string sql = "select * from school where name = @name";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@name", name)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    school = new School(id, name);
                }
            }
            LogHelper.AddLog($"根据名称查询了一个学校，名称：{name}");
            return school;
        }

        // 添加班级
        public static void AddClass(Class c)
        {
            string sql = "insert into class (id, name, school_id) values (@id, @name, @school_id)";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", c.Id),
                new SQLiteParameter("@name", c.Name),
                new SQLiteParameter("@school_id", c.School.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"添加了一个班级，编号：{c.Id}，名称：{c.Name}，所属学校：{c.School.Name}");
        }

        // 删除班级
        public static void DeleteClass(Class c)
        {
            string sql = "delete from class where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", c.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"删除了一个班级，编号：{c.Id}，名称：{c.Name}，所属学校：{c.School.Name}");
        }

        // 修改班级
        public static void UpdateClass(Class c)
        {
            string sql = "update class set name = @name, school_id = @school_id where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", c.Id),
                new SQLiteParameter("@name", c.Name),
                new SQLiteParameter("@school_id", c.School.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"修改了一个班级，编号：{c.Id}，名称：{c.Name}，所属学校：{c.School.Name}");
        }

        // 查询所有班级
        public static List<Class> GetAllClasses()
        {
            List<Class> classes = new List<Class>();
            string sql = "select * from class";
            using (SQLiteDataReader reader = ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    int school_id = reader.GetInt32(2);
                    School school = GetSchoolById(school_id);
                    Class c = new Class(id, name, school);
                    classes.Add(c);
                }
            }
            LogHelper.AddLog($"查询了所有班级");
            return classes;
        }

        // 根据编号查询班级
        public static Class GetClassById(int id)
        {
            Class c = null;
            string sql = "select * from class where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", id)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    string name = reader.GetString(1);
                    int school_id = reader.GetInt32(2);
                    School school = GetSchoolById(school_id);
                    c = new Class(id, name, school);
                }
            }
            LogHelper.AddLog($"根据编号查询了一个班级，编号：{id}");
            return c;
        }

        // 根据名称查询班级
        public static Class GetClassByName(string name)
        {
            Class c = null;
            string sql = "select * from class where name = @name";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@name", name)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    int school_id = reader.GetInt32(2);
                    School school = GetSchoolById(school_id);
                    c = new Class(id, name, school);
                }
            }
            LogHelper.AddLog($"根据名称查询了一个班级，名称：{name}");
            return c;
        }

        // 添加学生
        public static void AddStudent(Student s)
        {
            string sql = "insert into student (id, name, age, gender, class_id) values (@id, @name, @age, @gender, @class_id)";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", s.Id),
                new SQLiteParameter("@name", s.Name),
                new SQLiteParameter("@age", s.Age),
                new SQLiteParameter("@gender", s.Gender),
                new SQLiteParameter("@class_id", s.Class.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"添加了一个学生，编号：{s.Id}，姓名：{s.Name}，年龄：{s.Age}，性别：{s.Gender}，所属班级：{s.Class.Name}");
        }

        // 删除学生
        public static void DeleteStudent(Student s)
        {
            string sql = "delete from student where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", s.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"删除了一个学生，编号：{s.Id}，姓名：{s.Name}，年龄：{s.Age}，性别：{s.Gender}，所属班级：{s.Class.Name}");
        }

        // 修改学生
        public static void UpdateStudent(Student s)
        {
            string sql = "update student set name = @name, age = @age, gender = @gender, class_id = @class_id where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", s.Id),
                new SQLiteParameter("@name", s.Name),
                new SQLiteParameter("@age", s.Age),
                new SQLiteParameter("@gender", s.Gender),
                new SQLiteParameter("@class_id", s.Class.Id)
            };
            ExecuteNonQuery(sql, parameters);
            LogHelper.AddLog($"修改了一个学生，编号：{s.Id}，姓名：{s.Name}，年龄：{s.Age}，性别：{s.Gender}，所属班级：{s.Class.Name}");
        }

        // 查询所有学生
        public static List<Student> GetAllStudents()
        {
            List<Student> students = new List<Student>();
            string sql = "select * from student";
            using (SQLiteDataReader reader = ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    int age = reader.GetInt32(2);
                    string gender = reader.GetString(3);
                    int class_id = reader.GetInt32(4);
                    Class c = GetClassById(class_id);
                    Student s = new Student(id, name, age, gender);
                    s.Class = c;
                    students.Add(s);
                }
            }
            LogHelper.AddLog($"查询了所有学生");
            return students;
        }

        // 根据编号查询学生
        public static Student GetStudentById(int id)
        {
            Student s = null;
            string sql = "select * from student where id = @id";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@id", id)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    string name = reader.GetString(1);
                    int age = reader.GetInt32(2);
                    string gender = reader.GetString(3);
                    int class_id = reader.GetInt32(4);
                    Class c = GetClassById(class_id);
                    s = new Student(id, name, age, gender);
                    s.Class = c;
                }
            }
            LogHelper.AddLog($"根据编号查询了一个学生，编号：{id}");
            return s;
        }

        // 根据姓名查询学生
        public static Student GetStudentByName(string name)
        {
            Student s = null;
            string sql = "select * from student where name = @name";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@name", name)
            };
            using (SQLiteDataReader reader = ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    int age = reader.GetInt32(2);
                    string gender = reader.GetString(3);
                    int class_id = reader.GetInt32(4);
                    Class c = GetClassById(class_id);
                    s = new Student(id, name, age, gender);
                    s.Class = c;
                }
            }
            LogHelper.AddLog($"根据姓名查询了一个学生，姓名：{name}");
            return s;
        }
    }

}

